/*****************************************************************************/
/* Step 0. Preparations/Declarations                                         */
/*****************************************************************************/
SET NOCOUNT OFF

/*****************************************************************************/
/* Step 1. Remove stored procedure if it already exists                      */
/*****************************************************************************/
IF (SELECT OBJECT_ID('[ApplicationManagement].ApplicantWith')) IS NOT NULL
BEGIN
  DROP PROCEDURE [ApplicationManagement].[ApplicantWith]
END

GO

IF (SELECT OBJECT_ID('[ApplicationManagement].ApplicantWith')) IS NOT NULL
BEGIN
  DROP PROCEDURE [ApplicationManagement].[ApplicantWith]
END

GO
/*****************************************************************************/
/* Step 2. Create stored procedure                                           */
/*****************************************************************************/
CREATE PROCEDURE [ApplicationManagement].[ApplicantWith]( @ApplicantOID BIGINT )
AS 
BEGIN

	SELECT Applicants.[OID] as 'ApplicantsOID'
			,Applicants.[Version] as 'ApplicantsVersion'
			,Applicants.[KinshipID]
			,Applicants.[AddressID]
			,People.OID as 'PeopleOID' 
			,People.Version as 'PeopleVersion'
			,People.FirstName 
			,People.LastName 
			,People.MiddleInitial 
			,People.DOB 
			,People.Sex 
			,People.Phone1 
			,People.Phone2 
			,People.eMailAddress
	FROM Applicants
	INNER JOIN People ON Applicants.OID = People.OID
	WHERE Applicants.OID = @ApplicantOID

END

GO
/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [ApplicationManagement].[ApplicantWith] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership          
